from openpyxl import Workbook
import os
import xlrd
import copy
import tkinter
import tkinter.messagebox
from tkinter import filedialog
import time
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter


def find_file(folder):
    for i in os.listdir(folder):
        file = os.path.join(folder, i)
        if file.endswith('xls') or file.endswith('xlsx') or file.endswith('xlsm'):
            yield file


class ExcelData:
    def __init__(self, data_path):
        self.data_path = data_path
        self.data = xlrd.open_workbook(self.data_path)
        try:
            self.table = self.data.sheet_by_name('学生信息统计')
        except Exception as e:
            print(e, '请确认是否存在学生信息统计表')
        self.keys = self.table.row_values(0)
        self.rowNum = self.table.nrows
        self.colNum = self.table.ncols

    def readExcel(self):
        datas = []
        for i in range(1, self.rowNum):
            sheet_data = {}
            for j in range(self.colNum):
                c_cell = self.table.cell_value(i, j)
                sheet_data[self.keys[j]] = c_cell
            datas.append(sheet_data)
        return datas


def change(file):
    get_data = ExcelData(file)
    data = get_data.readExcel()
    for n, i in enumerate(data):
        del i['答题时长']
        if i['选择答案'] != '-':
            i['是否答对'] = '答对了' if i['选择答案'] == i['正确答案'] else '答得不对'
        else:
            i['是否答对'] = '-'
        del i['选择答案'], i['正确答案']
        if i['用户ID'] == '':
            i['用户ID'] = data[n-1]['用户ID']
        try:
            i['用户ID'] = '%d' % i['用户ID']
        except TypeError:
            i['用户ID'] = '%d' % eval(i['用户ID'])
        if i['管理员名称'] == '':
            i['管理员名称'] = data[n - 1]['管理员名称']
        if i['上课时间'] == '':
            i['上课时间'] = data[n - 1]['上课时间']
        if i['答题次数'] == '':
            i['答题次数'] = data[n - 1]['答题次数']
        if i['答题正确率'] == '':
            i['答题正确率'] = data[n - 1]['答题正确率']
        if i['答题卡ID'] == '':
            i['答题卡ID'] = data[n - 1]['答题卡ID']
        try:
            i['答题卡ID'] = '%d' % i['答题卡ID']
        except TypeError:
            pass
    card_id = sorted(list(set(map(lambda x: x['答题卡ID'], data))))
    card_tup = set(map(lambda x: (x['答题卡ID'], x['题号']), data))
    if '-' in card_id:
        card_id.remove('-')
    if ('-', '-') in card_tup:
        card_tup.remove(('-', '-'))
    dic = {}
    for card in card_id:
        card_max = max(list(map(lambda x: x[1] if x[0] == card else 0, card_tup)))
        dic[card] = int(card_max)
    dic1 = {'用户ID': '', '管理员名称': '', '上课时间': '', '答题次数':'', '答题正确率':''}
    help_dic = {}
    for n, i in enumerate(dic):
        dic1['答题卡%d' % (n+1)] = ''
        help_dic[i] = n+1
        for j in range(dic[i]):
            dic1['%d-%d' % (n+1, j+1)] = ''
    ls = []
    while data:
        d = copy.deepcopy(dic1)
        name = data[0]['用户ID']
        while 1:
            if not data:
                ls.append(d)
                break
            inf = data[0]
            if inf['用户ID'] == name:
                data.pop(0)
            else:
                ls.append(d)
                break
            d['上课时间'] = inf['上课时间']
            d['答题次数'] = inf['答题次数']
            d['答题正确率'] = inf['答题正确率']
            if d['用户ID'] == '':
                d['用户ID'] = inf['用户ID']
                d['管理员名称'] = inf['管理员名称']
            if inf['答题卡ID'] != '-':
                d['答题卡%d' % help_dic[inf['答题卡ID']]] = inf['答题卡ID']
                d['%d-%d' % (help_dic[inf['答题卡ID']], inf['题号'])] = inf['是否答对']
    for i in ls:
        print(i)
    return ls


def save_excel(li, folder):
    wb = Workbook()
    ws = wb.active
    count = 1
    ws.append(list(li[0].keys()))
    for i in li:
        ws.append(list(i.values()))
        count += 1
    for n, i in enumerate(li[0].keys()):
        if '答题卡' in i:
            ws.cell(row=1, column=n + 1).font = Font(bold=True)
            ws.column_dimensions[get_column_letter(n + 1)].width = 12
            for j in range(count):
                ws.cell(row=j + 1, column=n + 1).fill = PatternFill(fill_type='solid', start_color='33FF33')
    f = '学生成绩1.xlsx'
    if os.path.exists(folder + '\\' + f):
        file_id = eval(f[4])+1
        f = '学生成绩%d.xlsx' % file_id
    wb.save(folder+'\\'+f)


def select_path():
    path_ = filedialog.askdirectory()
    path.set(path_)


def main():
    folder = entry.get()
    new_folder = folder + r'\保存'
    if not os.path.exists(new_folder):
        os.makedirs(new_folder)
        tkinter.Label(root, text="已新建保存文件夹").grid(row=4, column=0)
    a = find_file(folder)
    count = 0
    t1 = time.time()
    for i in a:
        file = i.replace('/', '\\')
        try:
            li = change(file)
            count += 1
        except:
            tkinter.Label(root, text=f"文件{file}数据格式不匹配，读取失败").grid(row=4, column=0)
            continue
        save_excel(li, new_folder)
    t2 = time.time()
    tkinter.Label(root, text="已成功处理%d个表格文件，平均处理时长%.2f秒" % (count, (t2-t1)/count)).grid(row=5, column=0)
    print('处理完毕')


if __name__ == '__main__':
    root = tkinter.Tk()
    root.title('筛选工具')
    root['width'] = 280
    root['height'] = 110
    path = tkinter.StringVar()
    tkinter.Label(root, text="\t\t\t").grid(row=0, column=0)
    tkinter.Label(root, text="选择文件所在文件夹").grid(row=1, column=0)
    entry = tkinter.Entry(root, textvariable=path)
    entry.grid(row=2, column=0)
    tkinter.Button(root, text='选择', command=select_path).grid(row=2, column=1)
    tkinter.Button(root, text='开始', command=main).grid(row=3, column=0)
    tkinter.Label(root, text="\t\t\t").grid(row=4, column=0)
    tkinter.Label(root, text="\t\t\t").grid(row=5, column=0)
    root.mainloop()

